#include "mysqlconnection.h"
#include <QMessageBox>
MySqlConnection::MySqlConnection()
{
    connected = connect("root","");

}
MySqlConnection::~MySqlConnection(){
    DB.~QSqlDatabase();

}

bool MySqlConnection::connect(QString uid, QString pwd)
{
    try{
        QSqlDatabase _db=QSqlDatabase::database("mysql_connection_string");
        if(!_db.isValid())
        {
            _db=QSqlDatabase::addDatabase("QMYSQL", "mysql_connection_string");
            _db.setHostName("localhost");
            _db.setDatabaseName("QLBSach");
            _db.setUserName(uid);
            _db.setPassword(pwd);
        }
        if(!_db.open())return false;
        DB=_db;
        DB.close();
        _db.~QSqlDatabase();
        return true;
    }
    catch(QSqlError e)
    {
      throw e;
    }
}

bool MySqlConnection::connectiontest()
{
    try{
        QSqlDatabase _db=QSqlDatabase::database("mysql_connection_string");
        if(!_db.isValid())
        {
            _db=QSqlDatabase::addDatabase("QMYSQL", "mysql_connection_string");
            _db.setHostName("localhost");
            _db.setDatabaseName("QLBSach");
            _db.setUserName("root");
            _db.setPassword("123456");
        }
        if(!_db.open())
        {
             QMessageBox::critical(0,QObject::tr("Database Error"),_db.lastError().text());
             return false;
        }
        else
        _db.~QSqlDatabase();
        return true;
    }
    catch(QSqlError e)
    {
      throw e;
    }
}

QSqlQuery MySqlConnection::exec(QString query)
{
    try
        {
            DB.open();

            QSqlQuery qQuery = DB.exec(query);
            DB.exec();

            return qQuery;
        }
        catch(QSqlError e)
        {
            throw e;
        }
}

QSqlQueryModel *MySqlConnection::getTable(QString table)
{
    DB.open();

        QSqlQueryModel *model = new QSqlQueryModel();
        model->setQuery(table, DB);

        DB.close();

        return model;
}

QSqlQueryModel *MySqlConnection::DangNhap(QString user)
{
        DB.open();
        QSqlQueryModel *model = new QSqlQueryModel();
        model->setQuery("SELECT password FROM h2l_nguoidung Where username='"+user+"'", DB);
        DB.close();
        return model;
}
bool MySqlConnection::execQuery()
{      DB.open();
       query=new QSqlQuery(DB);

       query->exec();
       DB.close();
       if(query->isValid())
            return true;
       return false;
}

void MySqlConnection::Open()
{
    DB.open();
}

void MySqlConnection::Close()
{
    DB.close();
}
QStringList MySqlConnection::AutoComplete(QString table, QString fieldName)
{
    DB.open();
//    QSqlQueryModel *model = new QSqlQueryModel();
//    model->setQuery("Select DISTINCT "+fieldName+" from "+table);
//
//    return model->query().
    QSqlQuery query(DB);
    query.prepare("Select DISTINCT "+fieldName+" from "+table);
    QStringList tempList;
       while (query.next())
       {
           tempList<<query.value(0).toString();

       }
    DB.close();
    return tempList;



}

bool MySqlConnection::KiemTraTrung(QString TenBang, QString TenField, QString GiaTri)
{
    int i=exec("select "+TenField+" from "+TenBang+" where "+TenField+"='"+GiaTri+"'").size();
        if(i==0)
            return 0;
        return 1;
}

int MySqlConnection::giatTriQuyDinh(int maQuyDinh, bool &b)
{
    QSqlQuery result=exec("select giatri,apDung from h2l_QuyDinh where MaQD="+QString::number(maQuyDinh));
        result.first();
        b=result.value(1).toInt()==1?true:false;
        int values=result.value(0).toInt();
        return values;
}

QString MySqlConnection::sinhMa(QString tenBang,QString tenFied)
{
    QSqlQuery result=exec("select "+tenFied+"  from "+tenBang+" order by "+tenFied+" desc limit 0,1");
    result.first();
    int num=QString(result.value(0).toString().remove(0,1)).toInt()+1;
    qDebug()<<"Ma"<<result.value(0).toString();
    int length=QString::number(num).length();
    QString ma="00000";
    ma.resize(5-length);
    return ma.append(QString::number(num));
}
